package com.cm367.teamc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.faces.bean.ApplicationScoped;
import javax.faces.bean.ManagedBean;

import com.cm367.teamc.LeaveRequest;
import com.cm367.teamc.bean.Leave;
import com.cm367.teamc.util.DateUtil;

@ManagedBean
@ApplicationScoped
public class LeaveDAO {
	public List<LeaveRequest> getLeaveRequest(String userid, String type)
			throws SQLException {
		List<LeaveRequest> list = new ArrayList<LeaveRequest>();
		Calendar c = Calendar.getInstance();
		int year = c.get(Calendar.YEAR);
		String from = year + "-01-01";
		String to = year + "-12-31";
		String sql = "select * from leave where userid=? and type=? and datefrom>='"
				+ from + "' and dateto<='" + to + "'";
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, userid);
		ps.setString(2, type);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			LeaveRequest leave = new LeaveRequest();
			leave.setLeaveID(rs.getString("leaveid"));
			leave.setUserID(rs.getString("userid"));
			leave.setDateFrom(rs.getDate("datefrom"));
			leave.setDateTo(rs.getDate("dateto"));
			leave.setDays(rs.getInt("days"));
			leave.setReason(rs.getString("reason"));
			leave.setStatus(rs.getString("status"));
			leave.setLeaveType(rs.getString("type"));
			list.add(leave);
		}
		rs.close();
		ps.close();
		return list;
	}
	
	public List<LeaveRequest> getAllLeaveRequest(String userid, String type)
			throws SQLException {
		List<LeaveRequest> list = new ArrayList<LeaveRequest>();
		Calendar c = Calendar.getInstance();
		int year = c.get(Calendar.YEAR);
		String from = year + "-01-01";
		String to = year + "-12-31";
		String sql = "select * from leave where userid in (select userid from mapping where managerid=?) and type=? and datefrom>='"
				+ from + "' and dateto<='" + to + "'";
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, userid);
		ps.setString(2, type);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			LeaveRequest leave = new LeaveRequest();
			leave.setLeaveID(rs.getString("leaveid"));
			leave.setUserID(rs.getString("userid"));
			leave.setDateFrom(rs.getDate("datefrom"));
			leave.setDateTo(rs.getDate("dateto"));
			leave.setDays(rs.getInt("days"));
			leave.setReason(rs.getString("reason"));
			leave.setStatus(rs.getString("status"));
			leave.setLeaveType(rs.getString("type"));
			list.add(leave);
		}
		rs.close();
		ps.close();
		return list;
	}


	public List<Leave> getLeave(String userid, String type, String dateFrom,
			String dateTo) throws SQLException {
		List<Leave> leaves = new ArrayList<Leave>();
		String currentDate = DateUtil.getStringDate(new Date());
		String sql = "select * from leave where userid=? and type=? and datefrom<=? and dateto>=?";
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, userid);
		ps.setString(2, type);
		ps.setString(3, dateTo);
		ps.setString(4, dateFrom);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			int leaveID = rs.getInt("leaveid");
			String userID = rs.getString("userid");
			String df = rs.getString("datefrom");
			String dt = rs.getString("dateto");
			int days = rs.getInt("days");
			String reason = rs.getString("reason");
			String status = rs.getString("status");
			String remark = getRemark(status, currentDate, df, dt);
			int flag = getFlag(status, currentDate, df, dt);
			status = convertStatus(status);
			leaves.add(new Leave(leaveID, userID, df, dt, days, reason, status,
					type, remark, flag));
		}
		return leaves;
	}
	
	public List<Leave> getAllLeave(String userid, String type, String dateFrom,
			String dateTo) throws SQLException {
		List<Leave> leaves = new ArrayList<Leave>();
		String currentDate = DateUtil.getStringDate(new Date());
		String sql = "select * from leave where userid in (select userid from mapping where managerid=?) and type=? and datefrom<=? and dateto>=?";
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, userid);
		ps.setString(2, type);
		ps.setString(3, dateTo);
		ps.setString(4, dateFrom);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			int leaveID = rs.getInt("leaveid");
			String userID = rs.getString("userid");
			String df = rs.getString("datefrom");
			String dt = rs.getString("dateto");
			int days = rs.getInt("days");
			String reason = rs.getString("reason");
			String status = rs.getString("status");
			String remark = getRemark(status, currentDate, df, dt);
			int flag = getFlag(status, currentDate, df, dt);
			status = convertStatus(status);
			leaves.add(new Leave(leaveID, userID, df, dt, days, reason, status,
					type, remark, flag));
		}
		return leaves;
	}

	// Pending
	// Cancelled
	// Approved
	// Rejected
	// Cancel Leave
	// Approved Cancel Leave
	// Rejected Cancel Leave
	private String convertStatus(String status) {
		if (status.equals("Pending")) {
			return "Requested";
		}
		return status;
	}

	private String getRemark(String status, String currentDate, String df,
			String dt) {
		if (status.equals("Pending")) {
			// if (DateUtil.compareDate(currentDate, df) < 0) {
			// return "Waiting for Manager Approval";
			// } else {
			// return "Inactive";
			// }
			return "Waiting for Manager Approval";
		} else if (status.equals("Cancelled")) {
			return "You have cancelled the request";
		} else if (status.equals("Approved")) {
			int a = DateUtil.compareDate(currentDate, df);
			int b = DateUtil.compareDate(currentDate, dt);
			if (a < 0) {
				return "Request Approved";
			} else if (a == 0 || b <= 0) {
				return "Using";
			}
			return "Used";
		} else if (status.equals("Rejected")) {
			return "Request Rejected";
		} else if (status.equals("Cancel Leave")) {
			// int a = DateUtil.compareDate(currentDate, df);
			// if (a < 0) {
			// return "Waiting for Cancel Approval";
			// }
			// return "Cancel Inative";
			return "Waiting for Cancel Approval";
		} else if (status.equals("Approved Cancel Leave")) {
			return "Cancel Leave Approved";
		} else if (status.equals("Rejected Cancel Leave")) {
			return "Cancel Leave Rejected";
		}
		return null;
	}

	private int getFlag(String status, String currentDate, String df, String dt) {
		if (status.equals("Pending")) {
			if (DateUtil.compareDate(currentDate, df) < 0) {
				return 1;
			} else {
				// return 2;
				return 1;
			}
		} else if (status.equals("Cancelled")) {
			return 9;
		} else if (status.equals("Approved")) {
			int a = DateUtil.compareDate(currentDate, df);
			int b = DateUtil.compareDate(currentDate, dt);
			if (a < 0) {
				return 3;
			} else if (a == 0 || b <= 0) {
				return 9;
			}
			return 9;
		} else if (status.equals("Rejected")) {
			return 4;
		} else if (status.equals("Cancel Leave")) {
			int a = DateUtil.compareDate(currentDate, df);
			if (a < 0) {
				return 5;
			}
			// return 6;
			return 5;
		} else if (status.equals("Approved Cancel Leave")) {
			return 7;
		} else if (status.equals("Rejected Cancel Leave")) {
			return 8;
		}
		return 9;
	}

	public void changeStatus(String status, int leaveID) throws SQLException {
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("update leave set status=? where leaveid=?");
		ps.setString(1, status);
		ps.setInt(2, leaveID);
		ps.execute();
		ps.close();
	}

	// Used for manager
	public List<Leave> getUsersLeave(String dept) throws SQLException {
		List<Leave> leaves = new ArrayList<Leave>();
		String currentDate = DateUtil.getStringDate(new Date());
		String sql = "select leaveid,l.userid as userid,datefrom,dateto,days,reason,status,type from leave as l, user as u where l.userid=u.userid and department=? and (status=? or status=?) and datefrom>?";
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, dept);
		ps.setString(2, "Pending");
		ps.setString(3, "Cancel Leave");
		ps.setString(4, DateUtil.getYear(currentDate) + "-01-01");
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			int leaveID = rs.getInt("leaveid");
			String userID = rs.getString("userid");
			String df = rs.getString("datefrom");
			String dt = rs.getString("dateto");
			int days = rs.getInt("days");
			String reason = rs.getString("reason");
			String status = rs.getString("status");
			String type = rs.getString("type");
			int flag = 0;
			if (status.equals("Pending")) {
				flag = 11;
			} else {
				flag = 12;
			}
			leaves.add(new Leave(leaveID, userID, df, dt, days, reason, status,
					type, "", flag));
		}
		rs.close();
		ps.close();
		return leaves;
	}

	public boolean isLeaveValid(String userID, String dateFrom, String dateTo)
			throws SQLException {
		String sql = "select count(*) from leave where userid=? and (status=? or status=? or status=?) and datefrom<=? and dateto>=?";
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, userID);
		ps.setString(2, "Pending");
		ps.setString(3, "Approved");
		ps.setString(4, "Cancel Leave");
		ps.setString(5, dateTo);
		ps.setString(6, dateFrom);
		ResultSet rs = ps.executeQuery();
		rs.next();
		int count = rs.getInt(1);
		rs.close();
		ps.close();
		if (count > 0) {
			return false;
		}
		return true;
	}

	public boolean createLeave(String userid, String dateFrom, String dateTo,
			int days, String reason, String type) throws SQLException {
		String sql = "select count(*) from leave";
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		rs.next();
		int count = rs.getInt(1) + 1;
		rs.close();
		ps.close();
		sql = "insert into leave values(?,?,?,?,?,?,?,?)";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, count);
		ps.setString(2, userid);
		ps.setString(3, dateFrom);
		ps.setString(4, dateTo);
		ps.setInt(5, days);
		ps.setString(6, reason);
		ps.setString(7, "Pending");
		ps.setString(8, type);
		ps.execute();
		ps.close();
		return true;
	}

}
